<?php


Zend_Loader::loadClass("BaseDBRows");
Zend_Loader::loadClass("EmailMessage");
class EmailMessages extends BaseDBRows
{
	protected function setUp()
	{
		$this->_tableName = EmailMessage::_tablename;
	}	

	public function getOrgMessagesSource($orgId,$recipient="",$readStatus="",$dateFrom="",$dateTo="") {
	
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("em.*, orgsrc.name src_name, orgrec.name rec_name, left(em.message,50) message_part");
		$query->addFrom($this->_tableName . " em ");
		$query->addJoin(", " . Organization::_tablename . " orgsrc ");
		$query->addJoin(", " . Organization::_tablename . " orgrec ");
		$query->addWhere("em.source_org_id = orgsrc.org_id");
		$query->addWhere("em.recipient_org_id = orgrec.org_id");
		$query->addWhere("em.source_org_id = " . $orgId);
		$query->addWhere("not em.cancel");
		$query->addOrderBy("em.date_sent desc");
		
		// recipient
		if(strlen($recipient) > 0) {
			$query->addWhere("em.recipient_org_id = " . $recipient);
		}
		
		// read status
		if($readStatus == "1") {
			$query->addWhere("em.read = 1");	
		}
		if($readStatus == "2") {
			$query->addWhere("em.read = 0");
		}
		
		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" em.date_sent between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" em.date_sent >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" em.date_sent <= '" . $dateTo . "'");
		}
		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getOrgMessagesRecipient($orgId,$source="",$readStatus="",$dateFrom="",$dateTo="") {
		
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("em.*, orgsrc.name src_name, orgrec.name rec_name, left(em.message,50) message_part");
		$query->addFrom($this->_tableName . " em ");
		$query->addJoin(", " . Organization::_tablename . " orgsrc ");
		$query->addJoin(", " . Organization::_tablename . " orgrec ");
		$query->addWhere("em.source_org_id = orgsrc.org_id");
		$query->addWhere("em.recipient_org_id = orgrec.org_id");
		$query->addWhere("em.recipient_org_id = " . $orgId);
		$query->addWhere("not em.cancel");
		$query->addOrderBy("em.date_sent desc");
		
		// recipient
		if(strlen($source) > 0) {
			$query->addWhere("em.source_org_id = " . $source);
		}
		
		// read status
		if($readStatus == "1") {
			$query->addWhere("em.read = 1");	
		}
		if($readStatus == "2") {
			$query->addWhere("em.read = 0");
		}
		
		// dates from - to
		if(strlen($dateFrom) > 0 && strlen($dateTo) > 0) {
			$query->addWhere(" em.date_sent between least('" . $dateFrom . "','" . $dateTo . "') and greatest('" . $dateFrom . "','" . $dateTo . "')" );	
		}
		elseif(strlen($dateFrom) > 0) {
			$query->addWhere(" em.date_sent >= '" . $dateFrom . "'" );
		}
		elseif(strlen($dateTo) > 0) {
			$query->addWhere(" em.date_sent <= '" . $dateTo . "'");
		}
		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getOrgRecipients($orgId) {
		
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("distinct orgrec.org_id, orgrec.name");
		$query->addFrom($this->_tableName . " em ");
		$query->addJoin(", " . Organization::_tablename . " orgsrc ");
		$query->addJoin(", " . Organization::_tablename . " orgrec ");
		$query->addWhere("em.source_org_id = orgsrc.org_id");
		$query->addWhere("em.recipient_org_id = orgrec.org_id");
		$query->addWhere("orgsrc.org_id = " . $orgId);
		$query->addOrderBy("orgrec.name");
		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getOrgSenders($orgId) {
		
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("distinct orgsrc.org_id, orgsrc.name");
		$query->addFrom($this->_tableName . " em ");
		$query->addJoin(", " . Organization::_tablename . " orgsrc ");
		$query->addJoin(", " . Organization::_tablename . " orgrec ");
		$query->addWhere("em.source_org_id = orgsrc.org_id");
		$query->addWhere("em.recipient_org_id = orgrec.org_id");
		$query->addWhere("orgrec.org_id = " . $orgId);
		$query->addOrderBy("orgsrc.name");
		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
	public function getMailRecipients($orgType) {
	
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("org.org_id, org.name");
		$query->addFrom(Organization::_tablename . " org ");
		$query->addOrderBy("org_type, name");
		
		switch($orgType) {
			case CodesUtility::USER_TYPE_EXPORTER:
				$query->addWhere("org_type in ('Importer','Admin')");
				break;
			case CodesUtility::USER_TYPE_IMPORTER:
				$query->addWhere("org_type in ('Exporter','Admin')");
				break;
		}
		
		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->CreateSQL());
		return $result;
		
	}
	
	public function getMessageData($messageId) {

		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("em.*, orgsrc.name src_name, orgrec.name rec_name, left(em.message,50) message_part");
		$query->addFrom($this->_tableName . " em ");
		$query->addJoin(", " . Organization::_tablename . " orgsrc ");
		$query->addJoin(", " . Organization::_tablename . " orgrec ");
		$query->addWhere("em.source_org_id = orgsrc.org_id");
		$query->addWhere("em.recipient_org_id = orgrec.org_id");
		$query->addWhere("em.message_id = " . $messageId);
		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}
	
}
?>